<?php
/**
 * 客户风险管理
 *
 *@author  ZhangYe<banfg56@gmail.com>
 *@date    02/20/2017
 */
namespace Admin\Controller;
use Think\Controller;
use Common\Controller\BaseController;

class RiskController extends BaseController {

	public function index()
	{
		$MRisk = D('Admin/Risk');
		$res = array('rcode'=>1,'data'=>NULL, 'msg'=> 'OK');
		$psize = I('get.psize', $this->m_psize,'intval');
		$psize = (!$psize || $psize>20 ) ? $this->m_psize:$psize ;

		$status = I('get.status',-1, 'intval');
		$wheres = array();
		$wheres['_string'] = '1=1';
		$wheres['risk.is_delete'] = array('neq', 1 ); //没有逻辑删除的数据
		if( -1 != $status )
			$wheres['risk.status'] = array('eq', $status );
		$userphone = I('get.userphone','','trim');
		if( $userphone ){
			$maps = array();
			if(strlen($userphone) > 3 && is_numeric($userphone)) {
				$maps['risk.customer_phone'] = array('like','%'.$userphone.'%');
			}
			$maps['risk.customer_name'] = array('like','%'.$userphone.'%');
			$maps['customer.customer_code'] = array('like','%'.$userphone.'%');
			$maps['_logic'] = 'OR';
			$wheres['_complex'] = $maps;
				// $wheres['risk.customer_name'] = array('like', $userphone.'%');
		}
		//search time param
		$stime = I('get.start_time','','trim');
		$etime = I('get.end_time','','trim');
		if( $stime && $etime ){
			$tm = strtotime( $stime );
			$stime = date('Y-m-d 00:00:00', $tm );
			$tm = strtotime( $etime );
			$etime = date('Y-m-d 23:59:29', $tm );
			$wheres['risk.create_time'] = array('between', array($stime, $etime ) );
		}
		else if( $stime && !$etime ){
			$tm = strtotime( $stime );
			$stime = date('Y-m-d 00:00:00', $tm );
			$wheres['risk.create_time'] = array('gt', $stime );
		}
		else if( $etime && !$stime ){
			$tm = strtotime( $etime );
			$etime = date('Y-m-d 23:59:29', $tm );
			$wheres['risk.create_time'] = array('lt', $etime );
		}

		//search sales nameforce
		$sales = I('get.sales','','trim');
		if( $sales ){
			$MUser = D('Admin/User');
			$whereu = array();
			$whereu['nickname'] = array('like', '%'.$sales.'%');
			$salesSql = $MUser->field("user_id")->where( $whereu )->buildSql();
			$wheres['_string'] = 'risk.sales_id IN '.$salesSql;
		}

		$cnt = $MRisk->field("risk.*,sector.sector_name as company,user.nickname as manager_name,sale.nickname as sales_name")
					   ->join("LEFT JOIN sector on risk.sales_subcompany=sector.sector_id")
					   ->join("LEFT JOIN user on user.user_id=risk.sales_manager")
					   ->join("LEFT JOIN user as sale on sale.user_id=risk.sales_id")
					   ->join("LEFT JOIN customer on customer.customer_id = risk.customer_id")
					   ->where($wheres)
					   ->count();

		$Page = new \Common\ORG\Page( $cnt, $psize );


		$dlist = $MRisk->field("risk.*,sector.sector_name as company,user.nickname as manager_name,sale.nickname as sales_name")
					   ->join("LEFT JOIN sector on risk.sales_subcompany=sector.sector_id")
					   ->join("LEFT JOIN user on user.user_id=risk.sales_manager")
					   ->join("LEFT JOIN user sale on sale.user_id=risk.sales_id")
					   ->join("LEFT JOIN customer on customer.customer_id = risk.customer_id")
					   ->where($wheres)
					   ->order("risk.create_time desc ")
					   ->limit($Page->firstRow.','.$Page->listRows )
					   ->select();

		foreach ($dlist as &$vk) {
			$vk['show_phone'] = phone_mask($vk['customer_phone']);
			$where = array();
			$where['risk_id'] = $vk['risk_id'];
			$where['is_delete'] = 0;
			$time = M('track','risk_')->field("confirm_time,reply_time")->where($where)->order('confirm_time desc')->find();
			$vk['confirm_time'] = $time['confirm_time']?$time['confirm_time']:($time['reply_time']?$time['reply_time']:$vk['create_time']);
			/**Wjw 20170707修改浮窗开始**/
			if(!empty($vk['customer_id']))
				$vk['is_customer'] = 1;
			else
				$vk['is_customer'] = 2;
			/**修改结束**/
		}

		if( $dlist ){
			$res['rcode'] = 1;
			$res['data'] = $dlist;
			$res['pall'] =  $Page->getTotalPages();
			$res['p'] = $Page->nowPage;
			$res['psize'] = $psize;

		}else{
			$res['msg'] = "风险处理记录暂时没有数据";
		}

		e_json( $res );
	}

	/**
	 * 页面表单获取配置选项
	 *
	 */
	public function options()
	{
		$res = array('rcode'=>1,'data'=>NULL, 'msg'=> 'OK');
		$MSector = D('Sector','Service','Admin');
		$dataSub = $MSector->getSubCompany("sector_id,sector_name");
		$data['subcompany'] = $dataSub['data'];
		$res['data'] = $data;

		e_json( $res );
	}

	public function track_new() {
		$res = array('rcode'=>0,'data'=>NULL,'msg'=>'OK');

		/*风险情况*/
		$risk_id = I('post.risk_id','','intval');
		$status = I('post.status','','intval');
		$repeat = I('post.is_repeat');
		$is_repeat = $repeat == 'true'?1:0;
		$level = I('post.level','','intval');
		$MRisk = D('Risk','Service', 'Admin');

		/*反馈情况*/
		$reply = I('post.reply','','trim');
		$reply_confirm = I('post.reply_confirm','','trim');

		$Mdo = D('Admin/RiskTrack');
		$d['risk_id'] = $risk_id;
		$d['confirm_id'] = $this->m_user['user_id'];
		$d['reply_time'] = $d['confirm_time'] = date('Y-m-d H:i:s');
		$d['is_delete'] = 0;
		$d['reply_confirm'] = $reply_confirm;
		$d['reply'] = $reply;
		$d['last_status'] = $status;	//处理过程中添加处理状态
		$rid = $Mdo->data( $d )->add();	

		$dlist = $MRisk->_find( $risk_id );
		if($dlist['status'] == \Admin\Model\RiskModel::STATUS_CLOSE) {
			$res['msg'] = '该条追踪记录已经关闭,不能再进行操作';
			e_json($res);
		}
		
		$data = array();
		$data['status'] = $status;
		$data['is_repeat'] = $is_repeat;
		$data['level'] = $level;
		if($status == \Admin\Model\RiskModel::STATUS_CLOSE) {
			$data['confirm_time'] = date('Y-m-d');
		}
		$rcode = M('risk')->where("risk_id=%d",$risk_id)->save($data);
		if( $rcode || rid){
			$res['rcode'] = 1;
			$res['msg'] = '风险更新成功';
			$res['data'] = $rcode;
			$complainData = array();
			if(0 == $status)	$complainData['status'] = 0;	//未处理
			else if(1 == $status)	$complainData['status'] = 11;	//处理中
			else if(2 == $status)	$complainData['status'] = 21;	//已解决
			else if(3 == $status)	$complainData['status'] = 31;	//已挂起
			$complainData['up_time'] = date('Y-m-d H:i:s');	//更新上次处理回复时间
			$updateComplain = M("customer_complain")->where(array('risk_id'=>$risk_id))->save($complainData);
		}
		else{
			$res['msg'] = '风险更新失败';
		}
		e_json($res);
	}

	public function add()
	{
		$res = array('rcode'=>1,'data'=>NULL, 'msg'=> 'OK');

		if( IS_POST ){
			$sub_company = I('post.sales_company');
			$customer_id = I('post.customer_id', NULL, 'intval');
			$is_repeat = I('post.is_repeat') == 'true' ? 1:0;
			$add_data = array();
			$add_data['is_repeat'] = $is_repeat;
			$add_data['level'] = I('post.level',1, 'intval');
			$add_data['customer_name'] = I('post.customer_name','','trim');
			$add_data['customer_phone'] = I('post.customer_phone','','trim');

			//match customer by customer nickname or phone number
			$wherec = array();
			$MCustomer = D('Admin/Customer');
			if( $add_data['customer_name'] ){
				$wherec['nickname'] = array('like', $add_data['customer_name'].'%');
				$dataCustomer = $MCustomer->field("customer_id as uid")->where( $wherec )->select();
				if( $dataCustomer && 1== count( $dataCustomer) ){
					$add_data['customer_id'] = $dataCustomer[0]['uid'];
				}
			}
			if( $add_data['customer_phone'] ){
				$wherec = array();
				$wherec['phone'] = array('eq', $add_data['phone']);
				$dataCustomer = $MCustomer->field("customer_id as uid ")->where( $wherec )->select();
				if( $dataCustomer && 1==count( $dataCustomer ) ){
					$add_data['customer_id'] = $dataCustomer[0]['uid'];
				}
			}

			$add_data['sales_id'] = I('post.sales_id','','intval');
			$add_data['sales_sector'] = I('post.sales_sector','','intval');
			$add_data['sales_manager'] = I('post.sales_manager','','trim');
			$add_data['sales_subcompany'] =  $sub_company ? $sub_company['sector_id'] : NULL;
			$add_data['description'] = I('post.description','','trim');
			$add_data['feedback'] = I('post.feedback','','trim');
			$add_data['remark'] = I('post.remark','','trim');

			//call risk service
			$MRisk  = D('Risk','Service', 'Admin');
			$addRes = $MRisk->add( $add_data );

			if( $addRes && !$addRes['rcode'] ){
				$res['rcode'] = 0;
				$res['msg'] = $addRes['msg'];
			}
			else{
				$res['data'] = $addRes['data'];
			}
			e_json( $res );
		}
		else{
			//
		}
	}

	public function edit(){
		$res = array('rcode'=>0,'data'=>NULL, 'msg'=> 'OK');
		$risk_id = I('post.risk_id','','intval');
		/*risk表*/
		$customer_id = I('post.customer_id','','intval');
		$customer_name = I('post.customer_name','','trim');
		$customer_phone = I('post.customer_phone','','trim');
		$sales_id = I('post.sales_id','','intval');
		$sales_sector = I('post.sales_sector','','intval');
		$sales_manager = I('post.sales_manager','','intval');
		$sales_subcompany = I('post.sales_subcompany','','intval');
		$description = I('post.description','','trim');
		$level = I('post.level','','intval');
		$is_repeat = I('post.is_repeat') == 'true' ? 1:0;
		$remark = I('post.remark','','trim');
		/*risk表*/
		$reply = I('post.reply','','trim');

		$d = array();
		$d['customer_id'] = $customer_id;
		$d['customer_name'] = $customer_name;
		$d['customer_phone'] = $customer_phone;
		$d['sales_id'] = $sales_id;
		$d['sales_sector'] = $sales_sector;
		$d['sales_manager'] = $sales_manager;
		$d['sales_subcompany'] = $sales_subcompany;
		$d['description'] = $description;
		$d['is_repeat'] = $is_repeat;
		$d['level'] = $level;
		$d['customer_id'] = $customer_id;
		$d['remark'] = $remark;

		$m = array();
		$m['reply'] = $reply;

		$dlist1 = M('risk')->where("risk_id=%d",$risk_id)->save($d);
		$dlist2 = M('track','risk_')->where("risk_id=%d",$risk_id)->save($m);

		$res['rcode'] = 1;
		e_json($res);
	}

	public function confirm()
	{
		$res = array('rcode'=>0,'data'=>NULL, 'msg'=> 'OK');
		$MRisk  = D('Risk','Service', 'Admin');

		if( IS_GET ){
			$risk_id = I('get.id','0','intval');
			$dataConfirm = $MRisk->confirm( $risk_id );
		}
		else{
			$risk_id = I('post.risk_id','0','intval');
			$risk_confirm = I('post.confirm','','trim');
			$status = I('post.status','','trim');
			$dataConfirm = $MRisk->confirm( $risk_id, $risk_confirm,$status);
		}

		if( $dataConfirm && $dataConfirm['rcode'] ){
				$res['rcode'] = 1;
				$res['data'] = $dataConfirm['data'];
				$res['msg'] = 'OK';
			}
			else
				$res['msg'] = $dataConfirm['msg'];

		e_json( $res );
	}

	public function track()
	{
		$res = array('rcode'=>0,'data'=>NULL, 'msg'=> 'OK');
		$MRisk  = D('Risk','Service', 'Admin');

		if( IS_GET ){
			$id = I('get.id',0,'intval');
			$data = array();
			$data['risk'] = $MRisk->_find( $id );
			$data['track'] = $MRisk->recent( $id );
			if( $data['risk'] ){
				if( $data['is_delete'] )
					$res['msg'] = '数据已经删除';
				else if( $data['status'] == 2 )
					$res['msg'] = "风险已经处理完成";
				else{
					$res['rcode'] = 1;
					$res['track'] = $data['track'];
					$res['risk'] = $data['risk'];
				}
			}
			else
				$res['msg'] = "数据不存在";
 		}
		else{
			$Mdo = D('Admin/RiskTrack');
			$d = array();
			$risk_id = I('post.risk_id',0,'intval');
			$reply = I('post.reply','','trim');
			$confirm = I('post.reply_confirm','','trim');
			if( !$risk_id || !$reply || !$confirm ){
				$res['msg'] = "参数提交错误";
			}
			else{
				$d['risk_id'] = $risk_id;
				$d['confirm_id'] = $this->m_user['user_id'];
				$d['reply_time'] = $d['confirm_time'] = date('Y-m-d H:i:s');
				$d['is_delete'] = 0;
				$d['reply_confirm'] = $confirm;
				$d['reply'] = $reply;

				$rid = $Mdo->data( $d )->add();
				if( $rid ){
					$res['rcode'] = 1;
					$res['data'] = $MRisk->recent( $risk_id );
					$res['msg'] = "追踪记录添加成功";
				}
				else
					$res['msg'] = "追踪记录添加失败";
			}
		}

		e_json( $res );
	}

	//风险挂起或者取消挂起
	public function hangup()
	{
		$MRisk  = D('Risk','Service', 'Admin');

		if( IS_POST ){
			$id = I('post.id', 0, 'intval');
			$force = I('post.force',NULL,'intval');
			$dres = $MRisk->hangup( $id, $force);
			e_json( $dres );
		}
		else{
			//
		}
	}

	//关闭风险
	public function close()
	{
		$MRisk  = D('Risk','Service', 'Admin');

		if( IS_POST ){
			$id = I('post.id', 0, 'intval');
			$force = I('post.force',NULL,'intval');
			$dres = $MRisk->close( $id, $force );
			e_json( $dres );
		}
		else{
			//
		}
	}

	public function delete()
	{
		if( IS_POST ){
			$risk_id = I('post.id',0,'intval');
			$MRisk  = D('Risk','Service', 'Admin');
			$dres = $MRisk->remove( $risk_id );
			e_json( $dres );
		}
	}

	/*风险管理导入*/
	public function riskImport() {
		$res = array('rcode'=>0,'data'=>NULL,'msg'=>'OK');

		$upload = new \Vendor\Upload\Upload();
    	$upload->maxSize   =     3145728;
    	$upload->exts      =     array('xls', 'csv', 'xlsx');  
    	$upload->rootPath  =     './../www/uploads';
    	$upload->savePath  =      '/excel/';  
    	$info   =   $upload->upload();
    	if(!$info){
    		$this->error($upload->getError());
    	}else{
			$filename='./../www/uploads'.$info['file']['savepath'].$info['file']['savename'];
			
    		import("Vendor.PHPExcel.ExcelReader");
    		$ExcelReader=new \ExcelReader();
    		$shared = new \PHPExcel_Shared_Date();

			// $excel['start_time']      = date("Y-m-d",$shared ->ExcelToPHP($value[7]));
			// $time                     = $value[6]; 
			// $excel['end_time']        =  date("Y-m-d",strtotime("+".$time." months",$shared ->ExcelToPHP($value[7])));

    		$arr=$ExcelReader->reader_excel($filename);

			/*未能插入成功的数据*/
			// $addError = array();
			// $start_time = date('Y-m-01', strtotime(date("Y-m-d")));
			// $MTags = M('\Admin\Model\TagsModel:Tag','customer_');
			// $uinfo =$MTags->index();

			$dataError = array();
    		foreach ($arr as $key => $value) {
				$dataExcel = array();
				if(!$value['0'])  continue;
				/*risk*/
				if($value['1']){ 
					$dataExcel['customer_name'] = $value['1'];
				} else {
					array_push($dataError,array('id'=>$value['0'],'msg'=>'客户姓名不能为空'));
					continue;
				}

				if($value['2']){ 
					$dataExcel['customer_phone'] = $value['2'];
				} else {
					array_push($dataError,array('id'=>$value['0'],'msg'=>'客户联系方式不能为空'));
					continue;
				}

				if($value['3']){ 
					$dataExcel['status'] = $value['3']=='是'?2:1;
				} else {
					array_push($dataError,array('id'=>$value['0'],'msg'=>'结案状态不能为空'));
					continue;
				}

				if($value['5']){ 
					$dataExcel['is_repeat'] = $value['5']=='是'?1:0;
				} else {
					array_push($dataError,array('id'=>$value['0'],'msg'=>'是否重复投诉状态不能为空'));
					continue;
				}

				if($value['7']){ 
					$dataExcel['sales_id'] = $this->getUserIdByName($value['7']);
					if(!$dataExcel['sales_id']) {
						array_push($dataError,array('id'=>$value['0'],'msg'=>'业务员不存在'));
						continue;
					}
				} else {
					array_push($dataError,array('id'=>$value['0'],'msg'=>'业务员姓名不能为空'));
					continue;
				}
				
				if($value['8']){ 
					$dataExcel['sales_sector'] = $this->getSectorByName($value['8']);
					$dataExcel['sales_manager'] = $this->getUserIdByName($value['8']);
					if(!$dataExcel['sales_sector']) {
						array_push($dataError,array('id'=>$value['0'],'msg'=>'业务员所属部门不存在'));
						continue;
					}
					if(!$dataExcel['sales_manager']) {
						array_push($dataError,array('id'=>$value['0'],'msg'=>'业务经理不存在'));
						continue;
					}
				} else {
					array_push($dataError,array('id'=>$value['0'],'msg'=>'业务经理姓名不能为空'));
					continue;
				}

				if($value['9']){ 
					$dataExcel['sales_subcompany'] = $this->getSectorIdByName($value['9']);
					if(!$dataExcel['sales_subcompany']) {
						array_push($dataError,array('id'=>$value['0'],'msg'=>'所属分公司不存在'));
						continue;
					}
				} else {
					array_push($dataError,array('id'=>$value['0'],'msg'=>'团队名称不能为空'));
					continue;
				}

				if($value['10']){ 
					$dataExcel['description'] = $value['10'];
				} else {
					array_push($dataError,array('id'=>$value['0'],'msg'=>'风险情况说明不能为空'));
					continue;
				}

				if($value['11']){ 
					$dataExcel['level'] = sizeOf(explode("★",$value['11']))-1;
				} else {
					array_push($dataError,array('id'=>$value['0'],'msg'=>'风险程度不能为空'));
					continue;
				}

				if($value['1']){ 
					$dataExcel['customer_name'] = $value['1'];
				} else {
					array_push($dataError,array('id'=>$value['0'],'msg'=>'客户姓名为空'));
					continue;
				}

				$dataExcel['customer_id'] = $this->getCustomer($dataExcel['customer_name'],$dataExcel['customer_phone']);
				if(!$dataExcel['customer_id']) {
					array_push($dataError,array('id'=>$value['0'],'msg'=>'客户姓名不存在'));
					continue;
				}
				$dataExcel['create_time'] = date("Y-m-d",$shared ->ExcelToPHP($value['12']));

				/*判断是否已经存在该风险信息*/
				$whereRepeat = array();
				$whereRepeat['customer_id'] = $dataExcel['customer_id'];
				$whereRepeat['create_time'] = $dataExcel['create_time'];
				$dlistRepeat = M('risk')->field('risk_id')->where($whereRepeat)->find();
				if($dlistRepeat['risk_id']) {
					$dlistRepeat1 = M('risk')->where("risk_id='%d'",$dlistRepeat['risk_id'])->delete();
					$dlistRepeat2 = M('track','risk_')->where("risk_id='%d'",$dlistRepeat['risk_id'])->delete();
				}

				$d = array();
				$d['customer_id'] = $dataExcel['customer_id'];
				$d['customer_name'] = $dataExcel['customer_name'];
				$d['customer_phone'] = $dataExcel['customer_phone'];
				$d['sales_id'] = $dataExcel['sales_id'];
				$d['sales_sector'] = $dataExcel['sales_sector'];
				$d['sales_manager'] = $dataExcel['sales_manager'];
				$d['sales_subcompany'] = $dataExcel['sales_subcompany'];
				$d['description'] = $dataExcel['description'];
				$d['create_time'] = $dataExcel['create_time'];
				$d['level'] = $dataExcel['level'];
				$d['status'] = $dataExcel['status'];
				$d['is_repeat'] = $dataExcel['is_repeat'];
				
				$addID = M('risk')->data($d)->add();
				if($addID) {
					/*risk_track*/
					$length = sizeOf($value);
					$dataTrack = array();
					for($i = 12;$i <= ($length-3);$i+=3){
						$j = intval(floor(($i-12)/3));
						if($value[$i]) {
							$dataTrack[$j]['confirm_time'] = date("Y-m-d",$shared ->ExcelToPHP($value[$i]));
						}else {
							break;
						}
						if($value[$i+1]) {
							$dataTrack[$j]['reply_confirm'] = $value[$i+1];
						}else {
							unset($dataTrack[$j]);
							break;
						}
						if($value[$i+2]) {
							$dataTrack[$j]['reply'] = $value[$i+2];
						}else {
							unset($dataTrack[$j]);
							break;
						}
						$dataTrack[$j]['risk_id'] = $addID;
						$addIdTract = M('track','risk_')->data($dataTrack[$j])->add();
					}
				}
    		}
			$res['data'] = $dataError;
			if( !$res['data']) {
				$res['rcode'] = 1;
			}
    	}
		e_json($res);
	}

	/*********************************************导入功能辅助方法****************************************************/
	/*根据客户姓名和手机号查找客户ID*/
	public function getCustomer($customer_name,$customer_phone) {
		$where = array();
		$where['nickname'] = $customer_name;
		$where['phone'] = $customer_phone;
		$dlist = M('customer')->field('customer_id')->where($where)->find();
		return $dlist['customer_id'];
	}
	
	/*根据员工姓名获取员工ID*/
	public function getUserIdByName($nickname) {
		$dlist = M('user')->field('user_id')->where("nickname='%s'",$nickname)->find();
		return $dlist['user_id'];
	}

	/*根据部门名称获取部门ID*/
	public function getSectorIdByName($sector_name) {
		$dlist = M('sector')->field('sector_id')->where("sector_name='%s'",$sector_name)->find();
		return $dlist['sector_id'];
	}

	/*根据经理名字获取所属部门*/
	public function getSectorByName($nickname) {
		$dlist = M('user')->field('sector_id')->where("nickname='%s'",$nickname)->find();
		return $dlist['sector_id'];
	}

	/*********************************************导入功能辅助方法****************************************************/

	/*风险导出*/
	public function riskExport(){

		$MRisk = D('Admin/Risk');
		$res = array('rcode'=>1,'data'=>NULL, 'msg'=> 'OK');

		$status = I('get.status',-1, 'intval');
		$wheres = array();
		// $wheres['_string'] = '1=1';
		$wheres['risk.is_delete'] = array('neq', 1 ); //没有逻辑删除的数据
		if( -1 != $status )
			$wheres['risk.status'] = array('eq', $status );
		$userphone = I('get.userphone','','trim');
		// if( $userphone ){
		// 	if( preg_match('#^1[0-9]{10}$#', $userphone) ){
		// 		$wheres['risk.customer_phone'] = array('eq', $userphone);
		// 	}
		// 	else
		// 		$wheres['risk.customer_name'] = array('like', $userphone.'%');
		// }
		if( $userphone ){
			$maps = array();
			if(strlen($userphone) > 3 && is_numeric($userphone)) {
				$maps['risk.customer_phone'] = array('like','%'.$userphone.'%');
			}
			$maps['risk.customer_name'] = array('like','%'.$userphone.'%');
			$maps['customer.customer_code'] = array('like','%'.$userphone.'%');
			$maps['_logic'] = 'OR';
			$wheres['_complex'] = $maps;
				// $wheres['risk.customer_name'] = array('like', $userphone.'%');
		}
		//search time param
		$stime = I('get.start_time','','trim');
		$etime = I('get.end_time','','trim');
		if( $stime && $etime ){
			$tm = strtotime( $stime );
			$stime = date('Y-m-d 00:00:00', $tm );
			$tm = strtotime( $etime );
			$etime = date('Y-m-d 23:59:29', $tm );
			$wheres['risk.create_time'] = array('between', array($stime, $etime ) );
		}
		else if( $stime && !$etime ){
			$tm = strtotime( $stime );
			$stime = date('Y-m-d 00:00:00', $tm );
			$wheres['risk.create_time'] = array('gt', $stime );
		}
		else if( $etime && !$stime ){
			$tm = strtotime( $etime );
			$etime = date('Y-m-d 23:59:29', $tm );
			$wheres['risk.create_time'] = array('lt', $etime );
		}

		//search sales name
		$sales = I('get.sales','','trim');
		if( $sales ){
			$MUser = D('Admin/User');
			$whereu = array();
			$whereu['nickname'] = array('like', '%'.$sales.'%');
			$salesSql = $MUser->field("user_id")->where( $whereu )->buildSql();
			$wheres['_string'] = 'risk.sales_id IN '.$salesSql;
		}

		$dlist = $MRisk->field("risk.*,sector.sector_name as company,user.nickname as manager_name,sale.nickname as sales_name")
					   ->join("LEFT JOIN sector on risk.sales_subcompany=sector.sector_id")
					   ->join("LEFT JOIN user on user.user_id=risk.sales_manager")
					   ->join("LEFT JOIN user sale on sale.user_id=risk.sales_id")
					   ->join("LEFT JOIN customer on customer.customer_id = risk.customer_id")
					   ->where( $wheres )
					   ->order("risk.create_time desc")
					   ->select();

		foreach($dlist as &$value) {
			$where = array();
			$where['customer_id'] = $value['customer_id'];			
			$where['audit_res'] = 1;
			$value['track'] = M('track','risk_')->where("risk_id='%d'",$value['risk_id'])->select();
			$pays = M('pay_record')->field('sum(pay_mount) as pays')->where($where)->select();
			$value['pays'] = $pays[0]['pays']?$pays[0]['pays']:0;
		}

		$dlistLenght = array();
		foreach($dlist as $key => $value1) {
			$dlistLenght[$key] = sizeOf($value1['track']);
		}

		/*判断是否拥有高级导出功能*/
		$MRight = D('User','Service','Admin');
		$dright = $MRight->authWithKey($this->m_user['user_id'],'RISK_GAOJI');

		$maxLength = max($dlistLenght);

		import("Common.ORG.Excel");
		$SExcel = new \Common\ORG\Excel();
		$SSheet = new \Common\ORG\Sheet("风险管理数据");
		
		$column = array();
		if($dright['rcode'] || $this->m_is_admin) {
			$column = array('编号','客户姓名','客户电话','是否结案','结案时长（天）','是否重复投诉','交款金额','业务员','业务经理','团队','风险情况说明','风险程度');
		}else {
			$column = array('编号','客户姓名','是否结案','结案时长（天）','是否重复投诉','交款金额','业务员','业务经理','团队','风险情况说明','风险程度');
		}
		for($i = 0;$i < $maxLength;$i++) {array_push($column,($i+1).'次追踪','反馈','相关人员反馈');}
		$SSheet->setColumn( $column );

		if( $dlist){
			foreach($dlist as $key => $vd ){
				$row = array();
				$row[] = $key+1;
				$row[] = $vd['customer_name'];
				if($dright['rcode'] || $this->m_is_admin) {
					$row[] = $vd['customer_phone'];
				}
				$row[] = $vd['status'] == 0?'未处理':( $vd['status'] == 1?'正在处理中': ( $vd['status'] == 2?'处理完成': ( $vd['status'] == 3?'挂起':'关闭')));
				$row[] = $vd['status'] == 2? floor((strtotime($vd['finish_time'])-strtotime($vd['create_time']))/86400):'';
				$row[] = $vd['is_repeat'] == 1?'是':'否';
				$row[] = $vd['pays'];
				$row[] = $vd['sales_name'];
				$row[] = $vd['manager_name'];
				$row[] = $vd['company'];
				$row[] = $vd['description'];
				$row[] = $vd['level'] == 0?'':( $vd['level'] == 1?'★':( $vd['level'] == 2?'★★':( $vd['level'] == 3?'★★★':( $vd['level'] == 4?'★★★★':'★★★★★'))));
				foreach($vd['track'] as $v) {
					$row[] = $v['confirm_time'];
					$row[] = $v['reply_confirm'];
					$row[] = $v['reply'];
				}
				$SSheet->addRow( $row );
			}
		}
		$exportData = $SExcel->add_sheet( $SSheet)->export_sheet("风险管理数据导出");
		if( $exportData['rcode'] ){
			$exportData['name'] = basename( $exportData['data']['view_path'] );
			$exportData['excel'] = file_get_contents( $exportData['data']['real_path'] );
			$exportData['url'] = 'www.crm3.com/'.$exportData['data']['view_path'];
			echo $exportData['excel'];
			exit();
			
			// $res['rcode'] = 1;
			// $res['data'] = 'www.crm3.com/'.$exportData['data']['view_path'];
		}
	}

	/*获取自动补全员工信息*/
	public function getCustomers() {
		$res = array('rcode'=>0,'data'=>NULL, 'msg'=> 'OK');
		$where = array();
		// $dlist = M()->field('a.user_id,a.nickname,c.user_id as sales_manager,c.nickname as manager')
		// 			->table('user as a')
		// 			->join('sector as b on b.sector_id = a.sector_id')
		// 			->join('user as c on c.user_id = b.manager_id')
		// 			->where("a.is_deleted=%d",0)
		// 			->select();
		$userphone = I('get.customer','','trim');

		if( $userphone ){
			$maps = array();
			if(strlen($userphone) > 3 && is_numeric($userphone)) {
				$maps['a.phone'] = array('like','%'.$userphone.'%');
			}
			$maps['a.nickname'] = array('like','%'.$userphone.'%');
			$maps['a.customer_code'] = array('like','%'.$userphone.'%');
			$maps['_logic'] = 'OR';
			$where['_complex'] = $maps;
				// $wheres['risk.customer_name'] = array('like', $userphone.'%');
		}
		$where['_string'] = '1=1';
		// $where['a.combine_to'] = array('EQ',NULL);
		$dlist = M()->field('a.customer_id,a.nickname,a.phone,b.user_id as sales_id,b.nickname as sales_name,c.user_id as sales_manager,c.nickname as manager,d.sector_id as sales_sub_company,d.sector_name as subcompany')
					->table('customer as a')
					->join('user as b on b.user_id = a.sales_person')
					->join('user as c on c.user_id = a.sales_manager')
					->join('sector as d on d.sector_id = a.sales_sub_company')
					->where($where)
					->select();

		if($dlist) {
			$res['rcode'] = 1;
			$res['data'] = $dlist;
		}
		e_json($res);
	}





}
